package org.example.week10;

import org.example.week10.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    private Connection getConnection() throws SQLException {
        return DBUtil.getConnection();
    }

    public User getUserById(int id) {
        String sql = "SELECT * FROM users WHERE id = ?"; // SQL 查询语句
        try (Connection conn = getConnection(); // 获取连接
             PreparedStatement pstmt = conn.prepareStatement(sql)) { // 创建 PreparedStatement
            pstmt.setInt(1, id); // 设置参数
            try (ResultSet rs = pstmt.executeQuery()) { // 执行查询
                if (rs.next()) { // 处理结果
                    return new User(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getInt("pwd"),
                            rs.getInt("priv")
                    );
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null; // 如果没有找到用户，返回 null
    }

    public User getUserByName(String name) {
        String sql = "SELECT * FROM users WHERE name = ?"; // SQL 查询语句
        try (Connection conn = getConnection(); // 获取连接
             PreparedStatement pstmt = conn.prepareStatement(sql)) { // 创建 PreparedStatement
            pstmt.setString(1, name); // 设置参数
            try (ResultSet rs = pstmt.executeQuery()) { // 执行查询
                if (rs.next()) { // 处理结果
                    return new User(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getInt("pwd"),
                            rs.getInt("priv")
                    );
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null; // 如果没有找到用户，返回 null
    }

    // 示例：添加用户
    public boolean addUser(User user) {
        String sql = "INSERT INTO users (name, pwd, priv) VALUES (?, ?, ?)";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, user.getName());
            pstmt.setInt(2, user.getPwd());
            pstmt.setInt(3, user.getPriv());
            int rowsAffected = pstmt.executeUpdate(); // 执行更新
            return rowsAffected > 0; // 如果影响的行数大于 0，表示插入成功
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updateUser(User user) {
        String sql = "UPDATE users SET name = ?, pwd = ?, priv = ? WHERE id = ?";
        try (Connection conn = getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(4, user.getId());
                pstmt.setString(1, user.getName());
                pstmt.setInt(2, user.getPwd());
                pstmt.setInt(3, user.getPriv());
                int rowsAffected = pstmt.executeUpdate(); // 执行更新
                return rowsAffected > 0; // 如果影响的行数大于 0，表示更新成功
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean deleteUser(int id) {
        String sql = "DELETE FROM users WHERE id = ?";
        return tryUpdate(id, sql);
    }

    private boolean tryUpdate(int id, String sql) {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            int rowsAffected = pstmt.executeUpdate(); // 执行更新
            return rowsAffected > 0; // 如果影响的行数大于 0，表示删除成功
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    public boolean deleteUsers(int id) {
        String sql = "DELETE FROM users WHERE id = ?";
        return tryUpdate(id, sql);
    }

    public List<User> searchAllUsers() {
        String sql = "SELECT * FROM users";
        List<User> users = new ArrayList<>();
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            try (ResultSet rs = pstmt.executeQuery()) { // 执行查询
                while (rs.next()) { // 遍历查询结果
                    users.add(new User(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getInt("pwd"),
                            rs.getInt("priv")
                    ));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    public boolean accountDeactivation(int id) {
        String sql = "UPDATE users SET pwd = 3 WHERE id = ?";
        return tryUpdate(id, sql);
    }

}

